1. Field of the Invention
This invention relates to a new method for managing a database. More specifically, it relates to a method for assuring atomicity of multi-row update operations such as in a relational database system.
2. Description of the Prior Art
In prior art data management systems, support is sometimes provided for assuring the atomicity of operations effecting a database. Such an operation is "atomic" if the operation either succeeds completely or it fails, in which latter case the state of the database is left unchanged.
The IBM Information Management System (IMS/VS) Version 1 provides support for assuring the atomicity of operations updating one record, or row of a table, at a time. However, there is no multi-row update facility in IMS/VS.
Database management systems which provide multi-row updating operations include those based upon the relational model, such as the IBM Research System R, an experimental database management system, and the IBM Sequel Query Language/Data System (SQL/DS). System R is described in M. W. Blasgen, et al, "System R: An Architectural Overview", IBM System Journal, Vol. 20, No. 1, 1981, pages 41-62. The IBM SQL/DS is described in "SQL/Data System Planning and Administration", IBM Publication SH24-5014-0, Program Numbr 5748-XXJ, August 1981, with the recovery considerations set forth at pages 9-1 to 9-19. Hereafter, reference to relational databases will be intended to include all database management models which allow multi-row update operations.
The SQL language, which is the external language for access to databases managed by System R or SQL/DS, provides operations for modifying the state of userdefined data, including UPDATE, DELETE, and INSERT operations which allow the SQL user to insert, update, or delete multiple rows (i.e., records) in a specified database table. As implemented in System R and SQL/DS, SQL allows partial success of such multi-row operations, such that a detected error in the middle of a multi-row UPDATE, for example, will cause termination of the operation with only a subset of the required records updated. This leaves the table in an inconsistent state, and the application program requesting the SQL operation has no practical means of determining exactly which records were or were not updated. If recoverable files are used, a rollback, or recovery operation must be performed when such an error is detected to cause all work within the entire unit of recovery (UR), i.e. transaction, to be undone. Unfortunately, this action not only cancels the effects of the operation causing the error, but also the effects of any other operation in the same unit of recovery. The problem is more serious if non-recoverable files are in use. In such a case the rollback process has no effect, and the application programmer must handle the recovery of the data.
Various proposals have been made to avoid the necessity for backing out a complete transaction in the event of an error during a sequence of multi-row update operations. Thus, it has been suggested to "begin each complex operation with a savepoint and backing up to this savepoint" in the event of a failure during the operation. See, for example, Grey, et al, "The Recovery Manager of a Data Management System", IBM Research Publication, Computer Science RJ 2623 (#33801), Aug. 15, 1979. (See also, ACM Computing Surveys, Vol. 13, No. 2, June 1981, pages 223-242.) Grey, in this discussion of the System R, notes that such a savepoint technique was not implemented, but was rather an unsolved language design problem.